﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DataModel;

namespace SchoolDataLayer
{
   public class DlStaff
    {

       public int SaveStaffDetail(Staff _staff, FileData _file)
       {
           int id;
           using (SqlConnection conn = DBConn.GetConnectionString())
           {
               using (SqlCommand cmd = conn.CreateCommand())
               {
                   cmd.CommandText = "ProcAddStaff";
                   cmd.CommandType = CommandType.StoredProcedure;
                   cmd.Parameters.AddWithValue("@firstname",_staff.FirstName);
                   cmd.Parameters.AddWithValue("@lastname",_staff.LastName);
                   cmd.Parameters.AddWithValue("@address",_staff.Address);
                   cmd.Parameters.AddWithValue("@mobile",_staff.Mobile);
                   cmd.Parameters.AddWithValue("@phone",_staff.Phone);
                   cmd.Parameters.AddWithValue("@maritalstatus",_staff.MaritalStatus);
                   cmd.Parameters.AddWithValue("@dob",_staff.dob);
                   cmd.Parameters.AddWithValue("@citizenno",_staff.CitizenNo);
                   cmd.Parameters.AddWithValue("@dateofappointment",_staff.DateOfAppointment);
                   cmd.Parameters.AddWithValue("@FileName", _file.FileName);
                   cmd.Parameters.AddWithValue("@FilePath", _file.FilePath);
                   //cmd.Parameters.AddWithValue("@UserId", _file.UserId);
                   cmd.Parameters.AddWithValue("@uploadedon", DateTime.Now);
                   //cmd.Parameters.AddWithValue("@filename",_staff.cv);
                   return cmd.ExecuteNonQuery();
               }
           }
          // return id;
       }
    public List<Staff> GetAllStaff()
       {
           List<Staff> staff = new List<Staff>();
       using(SqlConnection conn=DBConn.GetConnectionString())
       {
       using(SqlCommand cmd=conn.CreateCommand())
       {
           cmd.CommandText = "ProcGetAllStaff";
           cmd.CommandType = CommandType.StoredProcedure;
           SqlDataReader reader = cmd.ExecuteReader();
           if (reader != null)
           {
               while (reader.Read())
               {
                   staff.Add(new Staff
                   {
                       FirstName = (string)reader["StaffName"],
                       StaffId = (int)reader["StaffId"]
                   });
               }
           }
           cmd.Dispose();
           reader.Dispose();
       }
    }
       return staff;
    }

    public int SaveAttendence(StaffAttendence _staffatt)
    {
        using (SqlConnection conn = DBConn.GetConnectionString())
        {
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = "ProcStaffAttendence";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@present", _staffatt.Present);
                cmd.Parameters.AddWithValue("@absent", _staffatt.Absent);
                cmd.Parameters.AddWithValue("@late", _staffatt.Late);
                cmd.Parameters.AddWithValue("@date", _staffatt.AttendDate);
                cmd.Parameters.AddWithValue("@staffid", _staffatt.StaffId);
                return cmd.ExecuteNonQuery();
            }
        }
    }
    public List<Staff> GetAllStaffDetail()
    {
        List<Staff> _staff = new List<Staff>();
        using (SqlConnection conn = DBConn.GetConnectionString())
        {
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = "";
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataReader reader = cmd.ExecuteReader();
                if(reader!=null)
                {
                    while (reader.Read())
                    {
                        _staff.Add(new Staff {
                            StaffId = (int)reader["StaffId"],
                            FirstName = (string)reader["StaffName"],
                            Address=(string)reader["Address"],
                            Mobile=(int)reader["Mobile"],
                            Phone=(int)reader["Phone"],
                            MaritalStatus = (string)reader["MaritalStatus"],
                            dob=(DateTime)reader["DOB"],
                            CitizenNo = (string)reader["CitizenshipNo"],
                            DateOfAppointment = (DateTime)reader["DateofAppointment"],
                            cv=(string)reader["CV"]
                        });
                    }
                }
                cmd.Dispose();
                reader.Dispose();
            }
        }
        return _staff;
    }

    public int SaveLoanAmout(Loan _loan) 
    {
        using (SqlConnection conn = DBConn.GetConnectionString())
        {
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = "ProcSaveLoan";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@loanAmount",_loan.LoanAmount);
                cmd.Parameters.AddWithValue("@interest",_loan.interestRate);
                cmd.Parameters.AddWithValue("@staffId", _loan.staffId);
                cmd.Parameters.AddWithValue("@appliedon", _loan.appliedDate);
                return cmd.ExecuteNonQuery();
            }
        }
    }

    public int GetPresentDays(int staffid,int month, int year)
    {
        int presentdays=0;
        using (SqlConnection conn = DBConn.GetConnectionString())
        {
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = "ProcPresentCount";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@staffid", staffid);
                cmd.Parameters.AddWithValue("@month", month);
                cmd.Parameters.AddWithValue("@year", year);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        presentdays = (int)reader["presentDays"];
                    }
                }
                cmd.Dispose();
                reader.Dispose();
            }
        }
        return presentdays;
    }
    public int GetAbsentDays(int staffid, int month, int year)
    {
        int absentdays = 0;
        using (SqlConnection conn = DBConn.GetConnectionString())
        {
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = "ProcAbsentCount";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@staffid", staffid);
                cmd.Parameters.AddWithValue("@month", month);
                cmd.Parameters.AddWithValue("@year", year);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        absentdays = (int)reader["absentDays"];
                    }
                }
                cmd.Dispose();
                reader.Dispose();
            }
        }
        return absentdays;
    }

    public int SaveSalary(StaffSal _staffsal)
    {
        using (SqlConnection conn = DBConn.GetConnectionString())
        {
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = "ProcSaveStaffSalary";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@staffId", _staffsal.StaffId);
                cmd.Parameters.AddWithValue("@createdDate", _staffsal.Date);
                cmd.Parameters.AddWithValue("@salary", _staffsal.SatffSalary);
                cmd.Parameters.AddWithValue("@bonus", _staffsal.Bonus);
                cmd.Parameters.AddWithValue("@deductedAmount", _staffsal.DeductedAmount);
                cmd.Parameters.AddWithValue("@totalamount", _staffsal.TotalAmount);
                cmd.Parameters.AddWithValue("@year", _staffsal.SalYear);
                cmd.Parameters.AddWithValue("@month", _staffsal.SalMonth);
                return cmd.ExecuteNonQuery();
            }
        }
    }

    public List<Loan> GetAllLoan()
    {
        List<Loan> loan = new List<Loan>();
        using (SqlConnection conn = DBConn.GetConnectionString())
        {
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = "ProcGetLoan";
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        loan.Add(new Loan {
                            staffId = (int)reader["StaffId"],
                            LoanAmount = Convert.ToDecimal(reader["LoanAmount"]),
                            interestRate=(int)reader["Interest"],
                            appliedDate = (DateTime)reader["AppliedDate"],
                           // returnedAmount = Convert.ToDecimal(reader["ReturnedAmount"]),
                           // remainingAmount = Convert.ToDecimal(reader["RemainingAmount"])
                             });
                    }
                }
                cmd.Dispose();
                reader.Dispose();
                return loan;
            }
        
        }
    }


    }
}
